IF EXISTS(  SELECT * FROM  sys.procedures WHERE name='sp_helptable')
BEGIN
    DROP PROC sp_helptable
END
go
CREATE PROCEDURE sp_helptable ( @table VARCHAR(100) )
-- exec sp_helptable tablename
-- SQL Server 没有提供函数或存储过程直接查看建表语句（），此存储过程可实现
AS
    BEGIN
        DECLARE @sql TABLE
            (
              s VARCHAR(1000) ,
              id INT IDENTITY
            );
-- 创建语句
        INSERT  INTO @sql  ( s )  VALUES  ( 'CREATE TABLE [' + @table + '] (' );
 
--获取注释
        SELECT  A.name AS table_name ,
                B.name AS column_name ,
                C.value AS column_description
        INTO    #columnsproperties
        FROM    sys.tables A
                INNER JOIN sys.columns B ON B.object_id = A.object_id
                LEFT JOIN sys.extended_properties C ON C.major_id = B.object_id
                                                       AND C.minor_id = B.column_id
        WHERE   A.name = @table;
 
-- 获取列的列表，拼接语句
        INSERT  INTO @sql  ( s )
                SELECT  '  [' + a.COLUMN_NAME + '] ' + DATA_TYPE
                        + COALESCE('('
                                   +  CASE WHEN DATA_TYPE='decimal' 
										THEN
								  CAST(a.NUMERIC_PRECISION AS VARCHAR)+','+CAST(a.NUMERIC_SCALE AS VARCHAR)
								  WHEN CHARACTER_MAXIMUM_LENGTH<0 THEN 'Max'
								  ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS  VARCHAR) END
                                   + ')', '') + ' '
                        + CASE WHEN EXISTS ( SELECT id
                                             FROM   syscolumns
                                             WHERE  OBJECT_NAME(id) = @table
                                                    AND name = a.COLUMN_NAME
                                                    AND COLUMNPROPERTY(id,
                                                              name,
                                                              'IsIdentity') = 1 )
                               THEN 'IDENTITY('
                                    + CAST(IDENT_SEED(@table) AS VARCHAR)
                                    + ','
                                    + CAST(IDENT_INCR(@table) AS VARCHAR)
                                    + ')'
                               ELSE ''
                          END + ' '
                        + ( CASE WHEN IS_NULLABLE = 'NO' THEN 'NOT '
                                 ELSE ''
                            END ) + 'NULL ' + COALESCE('DEFAULT '
                                                       + COLUMN_DEFAULT, '')
                        + CASE WHEN ISNULL(CONVERT(VARCHAR, b.column_description),
                                           '') <> ''
                               THEN ',/**'+ISNULL(CONVERT(VARCHAR, b.column_description),'')+'**/'
                               ELSE ','
                          END
                FROM    INFORMATION_SCHEMA.COLUMNS a
                        LEFT JOIN #columnsproperties b ON CONVERT(VARCHAR, a.COLUMN_NAME) = CONVERT(VARCHAR, b.column_name)
                WHERE   a.TABLE_NAME = @table
                ORDER BY ORDINAL_POSITION;
-- 主键
        DECLARE @pkname VARCHAR(100);
        SELECT  @pkname = CONSTRAINT_NAME
        FROM    INFORMATION_SCHEMA.TABLE_CONSTRAINTS
        WHERE   TABLE_NAME = @table
                AND CONSTRAINT_TYPE = 'PRIMARY KEY';
        IF ( @pkname IS NOT NULL )
            BEGIN
                INSERT  INTO @sql  ( s ) VALUES  ( '  PRIMARY KEY (' );
                INSERT  INTO @sql  ( s )
                        SELECT  '   [' + COLUMN_NAME + '],'
                        FROM    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                        WHERE   CONSTRAINT_NAME = @pkname
                        ORDER BY ORDINAL_POSITION;
    -- 去除尾部多余的字符
                UPDATE  @sql
                SET     s = LEFT(s, LEN(s) - 1)
                WHERE   id = @@identity;
                INSERT  INTO @sql ( s ) VALUES  ( '  )' );
            END;
        ELSE
            BEGIN
    -- 去除尾部多余的字符
                UPDATE  @sql
                SET     s = LEFT(s, LEN(s) - 1)
                WHERE   id = @@identity;
            END;
-- 继续拼接
        INSERT  INTO @sql ( s ) VALUES  ( ')' );
        
-- 输出结果
        SELECT  s AS CreateTableSQL
        FROM    @sql
        ORDER BY id;
    END;
